package com.navi.mould1.service.impl;

import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.halm_etaTrend_report.HalmEtaTrendReportGridOut;
import com.navi.bean.sql.SqlVo;
import com.navi.mould1.pojo.ToolToolgLineFabPojo;
import com.navi.mould1.pojo.WaferSpecPojo;
import com.navi.mould1.service.IHalmEtaTrendReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;
import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.util.DateUtils.getYearFirst;
import static com.navi.core.util.DateUtils.getYearLast;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;
import static com.navi.mould1.reportUtils.ComputeUtil.getAverageRate;
import static com.navi.mould1.reportUtils.ComputeUtil.getPercent;
import static com.navi.mould1.reportUtils.reportUtil.getCurrenDay;


@Slf4j
@Service
public class HalmEtaTrendReportServiceImpl implements IHalmEtaTrendReportService {


    public List<HalmEtaTrendReportGridOut> getGridData(ConditionBean conditions) {

        //优化查询速度，把Sql中子查询拿出来单独查
        List<String> tool_id_fk_list = new ArrayList<>();
        if (StringUtils.isNotEmpty(conditions.getToolg_id()) || StringUtils.isNotEmpty(conditions.getTool_type()) || StringUtils.isNotEmpty(conditions.getTool_id())) {
            SqlVo sql = new SqlVo("select tool_id as tool_id_fk from bis_tool where 1=1");
            if (StringUtils.isNotEmpty(conditions.getToolg_id())) sql.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + ")");
            if (StringUtils.isNotEmpty(conditions.getTool_type())) sql.appendSql("and unit_typ in" + jointInSql(conditions.getTool_type()));
            if (StringUtils.isNotEmpty(conditions.getTool_id())) sql.appendSql("and tool_id in" + jointInSql(conditions.getTool_id()));
            List<ToolToolgLineFabPojo> list = mySql_QueryRunner_QueryListBySql(sql, ToolToolgLineFabPojo.class);
            tool_id_fk_list.addAll(list.stream().map(data -> data.getTool_id_fk()).distinct().collect(Collectors.toList()));
        }
        List<String> wafer_spec_list = new ArrayList<>();
        if (StringUtils.isNotEmpty(conditions.getSpec()) || StringUtils.isNotEmpty(conditions.getWafer_spec())) {
            SqlVo sql = new SqlVo("select data_id as wafer_spec from bis_data where data_cate = 'WFSF'");
            if (StringUtils.isNotEmpty(conditions.getSpec())) sql.appendSql("and data_ext in" + jointInSql(conditions.getSpec()));
            if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sql.appendSql("and data_id in" + jointInSql(conditions.getWafer_spec()));
            List<WaferSpecPojo> list = mySql_QueryRunner_QueryListBySql(sql, WaferSpecPojo.class);
            wafer_spec_list.addAll(list.stream().map(data -> data.getWafer_spec()).distinct().collect(Collectors.toList()));
        }

        List<HalmEtaTrendReportGridOut> fianl_gridOut_list = new ArrayList<>();

        final SqlVo sqlVo1 = new SqlVo();
        sqlVo1.appendSql("SELECT");
        sqlVo1.appendSql("input_date as date,");
        sqlVo1.appendSql("input_time as hour,");
        sqlVo1.appendSql("good_eta_sum AS eta_value,");
        sqlVo1.appendSql("good_count AS count");
        sqlVo1.appendSql("FROM");
        sqlVo1.appendSql("icim_jn.halm_test_data_summary A");
        sqlVo1.appendSql("WHERE 1=1");
        //if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo1.appendSql("AND A.TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where fab_id_fk in" + jointInSql(conditions.getFab_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo1.appendSql("AND A.LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
        //if (StringUtils.isNotEmpty(conditions.getToolg_id())) sqlVo1.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + "))");
        //if (StringUtils.isNotEmpty(conditions.getTool_type())) sqlVo1.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where unit_typ in" + jointInSql(conditions.getTool_type()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo1.appendSql("AND A.TOOL_ID_FK in" + jointInSql(conditions.getTool_id()));
        if (CollectionUtils.isNotEmpty(tool_id_fk_list)) sqlVo1.appendSql("AND A.TOOL_ID_FK in" + tool_id_fk_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        //if (StringUtils.isNotEmpty(conditions.getSpec())) sqlVo1.appendSql("AND A.WAFER_SPEC in(select data_id from bis_data where data_cate = 'WFSF' and data_ext in" + jointInSql(conditions.getSpec()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sqlVo1.appendSql("AND A.WAFER_SPEC in" + jointInSql(conditions.getWafer_spec()));
        if (CollectionUtils.isNotEmpty(wafer_spec_list)) sqlVo1.appendSql("AND A.WAFER_SPEC in" + wafer_spec_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        if (StringUtils.isNotEmpty(conditions.getManufacturer())) sqlVo1.appendSql("AND A.CUS_ID_FK in" + jointInSql(conditions.getManufacturer()));
        if (StringUtils.isNotEmpty(conditions.getBase_id())) sqlVo1.appendSql("AND A.BASE_ID in" + jointInSql(conditions.getBase_id()));
        //if (StringUtils.isNotEmpty(conditions.getAttributes())) sqlVo1.appendSql("AND A.ATTRIBUTES in" + jointInSql(conditions.getAttributes()));
        if (StringUtils.isNotEmpty(conditions.getLot_id())) sqlVo1.appendSql("AND A.LOT_ID in" + jointInSql(conditions.getLot_id()));
        sqlVo1.appendSqlAndAddParam("AND INPUT_DATE >= ?", new Timestamp(getYearFirst(Integer.parseInt(conditions.getStart_time().substring(0, 4))).getTime()).toString().substring(0, 10));
        sqlVo1.appendSqlAndAddParam("AND INPUT_DATE <= ?", new Timestamp(getYearLast(Integer.parseInt(conditions.getStart_time().substring(0, 4))).getTime()).toString().substring(0 ,10));

        List<HalmEtaTrendReportGridOut> byMonthResultDatas = new ArrayList<>();

        mySql_QueryRunner_QueryListBySql(sqlVo1, HalmEtaTrendReportGridOut.class).stream()
                .collect(Collectors.groupingBy(data -> getCurrenDay(data.getDate(), data.getHour()).substring(0, 7)))
                .forEach((key, list) -> {
                    HalmEtaTrendReportGridOut gridOut = new HalmEtaTrendReportGridOut();
                    gridOut.setDate(getCurrenDay(list.get(0).getDate(), list.get(0).getHour()).substring(0, 7));
                    gridOut.setEta_value(list.stream().mapToDouble(data -> data.getEta_value()).sum());
                    gridOut.setCount(list.stream().mapToInt(data -> data.getCount()).sum());
                    byMonthResultDatas.add(gridOut);
                });

        //by Year 数据
        if ("2018".equals(conditions.getStart_time().substring(0, 4))) {
            get2018YearData(conditions, fianl_gridOut_list);
        }else {
            byMonthResultDatas.stream().collect(Collectors.groupingBy(data -> data.getDate().toString().substring(0, 4))).forEach((year, by_year_list) -> {
                HalmEtaTrendReportGridOut year_gridOut = new HalmEtaTrendReportGridOut();
                year_gridOut.setYear(year);
                year_gridOut.setEta_rate(getAverageRate(by_year_list.stream().mapToDouble(data -> data.getEta_value()).sum(), by_year_list.stream().mapToInt(data -> data.getCount()).sum(), 2));
                year_gridOut.setEta_percent(getPercent((year_gridOut.getEta_rate()/100)));
                fianl_gridOut_list.add(year_gridOut);
            });
        }
        //by Month 数据
        if ("2018".equals(conditions.getStart_time().substring(0, 4))) get2018MonthData(conditions, fianl_gridOut_list);
        byMonthResultDatas.stream().filter(data -> !"2018-11".equals(data.getDate())).sorted(Comparator.comparing(data -> data.getDate().substring(5))).forEach(data -> {
            HalmEtaTrendReportGridOut month_gridOut = new HalmEtaTrendReportGridOut();
            month_gridOut.setMonth(getMonthCode(data.getDate().substring(5)));
            month_gridOut.setEta_rate(getAverageRate(data.getEta_value(), data.getCount(),2));
            month_gridOut.setEta_percent(getPercent((month_gridOut.getEta_rate()/100)));
            fianl_gridOut_list.add(month_gridOut);
        });

        //--------------------------------------------------------------------------------------------------------------------------------------------------------------

        final SqlVo sqlVo2 = new SqlVo();
        sqlVo2.appendSql("SELECT");
        sqlVo2.appendSql("input_date as date,");
        sqlVo2.appendSql("input_time as hour,");
        sqlVo2.appendSql("line_id,");
        sqlVo2.appendSql("line_id_fk,");
        sqlVo2.appendSql("good_eta_sum AS eta_value,");
        sqlVo2.appendSql("good_count AS count");
        sqlVo2.appendSql("FROM");
        sqlVo2.appendSql("icim_jn.halm_test_data_summary A");
        sqlVo2.appendSql("WHERE 1=1");
        //if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo2.appendSql("AND A.TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where fab_id_fk in" + jointInSql(conditions.getFab_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo2.appendSql("AND A.LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
        //if (StringUtils.isNotEmpty(conditions.getToolg_id())) sqlVo2.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + "))");
        //if (StringUtils.isNotEmpty(conditions.getTool_type())) sqlVo2.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where unit_typ in" + jointInSql(conditions.getTool_type()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo2.appendSql("AND A.TOOL_ID_FK in" + jointInSql(conditions.getTool_id()));
        if (CollectionUtils.isNotEmpty(tool_id_fk_list)) sqlVo2.appendSql("AND A.TOOL_ID_FK in" + tool_id_fk_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        //if (StringUtils.isNotEmpty(conditions.getSpec())) sqlVo2.appendSql("AND A.WAFER_SPEC in(select data_id from bis_data where data_cate = 'WFSF' and data_ext in" + jointInSql(conditions.getSpec()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sqlVo2.appendSql("AND A.WAFER_SPEC in" + jointInSql(conditions.getWafer_spec()));
        if (CollectionUtils.isNotEmpty(wafer_spec_list)) sqlVo2.appendSql("AND A.WAFER_SPEC in" + wafer_spec_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        if (StringUtils.isNotEmpty(conditions.getManufacturer())) sqlVo2.appendSql("AND A.CUS_ID_FK in" + jointInSql(conditions.getManufacturer()));
        if (StringUtils.isNotEmpty(conditions.getBase_id())) sqlVo2.appendSql("AND A.BASE_ID in" + jointInSql(conditions.getBase_id()));
        //if (StringUtils.isNotEmpty(conditions.getAttributes())) sqlVo2.appendSql("AND A.ATTRIBUTES in" + jointInSql(conditions.getAttributes()));
        if (StringUtils.isNotEmpty(conditions.getLot_id())) sqlVo2.appendSql("AND A.LOT_ID in" + jointInSql(conditions.getLot_id()));
        sqlVo2.appendSqlAndAddParam("AND CONCAT(INPUT_DATE, ' ', INPUT_TIME, ':00:00') >= ?", conditions.getStart_time()+":00:00");
        sqlVo2.appendSqlAndAddParam("AND CONCAT(INPUT_DATE, ' ', INPUT_TIME, ':00:00') < ?", conditions.getEnd_time()+":00:00");

        List<HalmEtaTrendReportGridOut> byLineResultDatas = new ArrayList<>();

        mySql_QueryRunner_QueryListBySql(sqlVo2, HalmEtaTrendReportGridOut.class).stream()
                .filter(data -> data.getLine_id_fk() != null)
                .collect(Collectors.groupingBy(data -> getCurrenDay(data.getDate(), data.getHour()) + data.getLine_id_fk()))
                .forEach((key, list) -> {
                    HalmEtaTrendReportGridOut gridOut = new HalmEtaTrendReportGridOut();
                    gridOut.setDay(getCurrenDay(list.get(0).getDate(), list.get(0).getHour()));
                    gridOut.setLine_id(list.get(0).getLine_id()==null?"":list.get(0).getLine_id());
                    gridOut.setLine_id_fk(list.get(0).getLine_id_fk());
                    gridOut.setEta_value(list.stream().mapToDouble(data -> data.getEta_value()).sum());
                    gridOut.setCount(list.stream().mapToInt(data -> data.getCount()).sum());
                    byLineResultDatas.add(gridOut);
                });

        //by Day 数据
        byLineResultDatas.stream().collect(Collectors.groupingBy(data -> data.getDay())).forEach((day, by_day_list) -> {
            HalmEtaTrendReportGridOut day_gridOut = new HalmEtaTrendReportGridOut();
            day_gridOut.setDay(day);
            day_gridOut.setEta_rate(getAverageRate(by_day_list.stream().mapToDouble(data -> data.getEta_value()).sum(), by_day_list.stream().mapToInt(data -> data.getCount()).sum(), 2));
            day_gridOut.setEta_percent(getPercent((day_gridOut.getEta_rate()/100)));
            fianl_gridOut_list.add(day_gridOut);
        });
        //by 线别数据分组
        byLineResultDatas.stream().filter(data -> data.getLine_id_fk() != null).forEach(data -> {
            HalmEtaTrendReportGridOut line_gridOut = new HalmEtaTrendReportGridOut();
            line_gridOut.setDay(data.getDay());
            line_gridOut.setLine_id_fk(data.getLine_id_fk());
            line_gridOut.setLine_id(data.getLine_id());
            line_gridOut.setEta_rate(getAverageRate(data.getEta_value(), data.getCount(), 2));
            line_gridOut.setEta_percent(getPercent((line_gridOut.getEta_rate()/100)));
            fianl_gridOut_list.add(line_gridOut);
        });

        return fianl_gridOut_list;
    }






    /*** 2018 年ETA平均数值写死(区分单多晶)**/
    private void get2018YearData(ConditionBean conditions, List<HalmEtaTrendReportGridOut> gridOut_list) {
        HalmEtaTrendReportGridOut year_gridOut = new HalmEtaTrendReportGridOut();
        year_gridOut.setYear("2018");
        if ("单晶".equals(conditions.getAttributes())) {
            year_gridOut.setEta_rate(21.77);
            year_gridOut.setEta_percent("21.77%");
            gridOut_list.add(year_gridOut);
        }else if ("多晶".equals(conditions.getAttributes())) {
            year_gridOut.setEta_rate(19.18);
            year_gridOut.setEta_percent("19.18");
            gridOut_list.add(year_gridOut);
        }
    }


    /*** 2018 1月-12月ETA平均数值写死(区分单多晶)**/
    private void get2018MonthData(ConditionBean conditions, List<HalmEtaTrendReportGridOut> gridOut_list) {
        if ("单晶".equals(conditions.getAttributes())) {
            HalmEtaTrendReportGridOut month_1_gridOut = new HalmEtaTrendReportGridOut();
            month_1_gridOut.setMonth(getMonthCode("01"));
            month_1_gridOut.setEta_rate(21.70);
            month_1_gridOut.setEta_percent("21.70%");
            gridOut_list.add(month_1_gridOut);

            HalmEtaTrendReportGridOut month_2_gridOut = new HalmEtaTrendReportGridOut();
            month_2_gridOut.setMonth(getMonthCode("02"));
            month_2_gridOut.setEta_rate(21.71);
            month_2_gridOut.setEta_percent("21.71%");
            gridOut_list.add(month_2_gridOut);

            HalmEtaTrendReportGridOut month_3_gridOut = new HalmEtaTrendReportGridOut();
            month_3_gridOut.setMonth(getMonthCode("03"));
            month_3_gridOut.setEta_rate(21.74);
            month_3_gridOut.setEta_percent("21.74%");
            gridOut_list.add(month_3_gridOut);

            HalmEtaTrendReportGridOut month_4_gridOut = new HalmEtaTrendReportGridOut();
            month_4_gridOut.setMonth(getMonthCode("04"));
            month_4_gridOut.setEta_rate(21.74);
            month_4_gridOut.setEta_percent("21.74%");
            gridOut_list.add(month_4_gridOut);

            HalmEtaTrendReportGridOut month_5_gridOut = new HalmEtaTrendReportGridOut();
            month_5_gridOut.setMonth(getMonthCode("05"));
            month_5_gridOut.setEta_rate(21.71);
            month_5_gridOut.setEta_percent("21.71%");
            gridOut_list.add(month_5_gridOut);

            HalmEtaTrendReportGridOut month_6_gridOut = new HalmEtaTrendReportGridOut();
            month_6_gridOut.setMonth(getMonthCode("06"));
            month_6_gridOut.setEta_rate(21.77);
            month_6_gridOut.setEta_percent("21.77%");
            gridOut_list.add(month_6_gridOut);

            HalmEtaTrendReportGridOut month_8_gridOut = new HalmEtaTrendReportGridOut();
            month_8_gridOut.setMonth(getMonthCode("08"));
            month_8_gridOut.setEta_rate(21.91);
            month_8_gridOut.setEta_percent("21.91%");
            gridOut_list.add(month_8_gridOut);

            HalmEtaTrendReportGridOut month_9_gridOut = new HalmEtaTrendReportGridOut();
            month_9_gridOut.setMonth(getMonthCode("09"));
            month_9_gridOut.setEta_rate(21.95);
            month_9_gridOut.setEta_percent("21.95%");
            gridOut_list.add(month_9_gridOut);

            HalmEtaTrendReportGridOut month_10_gridOut = new HalmEtaTrendReportGridOut();
            month_10_gridOut.setMonth(getMonthCode("10"));
            month_10_gridOut.setEta_rate(21.79);
            month_10_gridOut.setEta_percent("21.79%");
            gridOut_list.add(month_10_gridOut);

            HalmEtaTrendReportGridOut month_11_gridOut = new HalmEtaTrendReportGridOut();
            month_11_gridOut.setMonth(getMonthCode("11"));
            month_11_gridOut.setEta_rate(21.72);
            month_11_gridOut.setEta_percent("21.72%");
            gridOut_list.add(month_11_gridOut);
        }else if ("多晶".equals(conditions.getAttributes())) {
            HalmEtaTrendReportGridOut month_1_gridOut = new HalmEtaTrendReportGridOut();
            month_1_gridOut.setMonth(getMonthCode("01"));
            month_1_gridOut.setEta_rate(19.11);
            month_1_gridOut.setEta_percent("19.11%");
            gridOut_list.add(month_1_gridOut);

            HalmEtaTrendReportGridOut month_2_gridOut = new HalmEtaTrendReportGridOut();
            month_2_gridOut.setMonth(getMonthCode("02"));
            month_2_gridOut.setEta_rate(19.10);
            month_2_gridOut.setEta_percent("19.10%");
            gridOut_list.add(month_2_gridOut);

            HalmEtaTrendReportGridOut month_3_gridOut = new HalmEtaTrendReportGridOut();
            month_3_gridOut.setMonth(getMonthCode("03"));
            month_3_gridOut.setEta_rate(19.11);
            month_3_gridOut.setEta_percent("19.11%");
            gridOut_list.add(month_3_gridOut);

            HalmEtaTrendReportGridOut month_4_gridOut = new HalmEtaTrendReportGridOut();
            month_4_gridOut.setMonth(getMonthCode("04"));
            month_4_gridOut.setEta_rate(19.12);
            month_4_gridOut.setEta_percent("19.12%");
            gridOut_list.add(month_4_gridOut);

            HalmEtaTrendReportGridOut month_5_gridOut = new HalmEtaTrendReportGridOut();
            month_5_gridOut.setMonth(getMonthCode("05"));
            month_5_gridOut.setEta_rate(19.14);
            month_5_gridOut.setEta_percent("19.14%");
            gridOut_list.add(month_5_gridOut);

            HalmEtaTrendReportGridOut month_6_gridOut = new HalmEtaTrendReportGridOut();
            month_6_gridOut.setMonth(getMonthCode("06"));
            month_6_gridOut.setEta_rate(19.17);
            month_6_gridOut.setEta_percent("19.17%");
            gridOut_list.add(month_6_gridOut);

            HalmEtaTrendReportGridOut month_7_gridOut = new HalmEtaTrendReportGridOut();
            month_7_gridOut.setMonth(getMonthCode("07"));
            month_7_gridOut.setEta_rate(19.20);
            month_7_gridOut.setEta_percent("19.20%");
            gridOut_list.add(month_7_gridOut);

            HalmEtaTrendReportGridOut month_8_gridOut = new HalmEtaTrendReportGridOut();
            month_8_gridOut.setMonth(getMonthCode("08"));
            month_8_gridOut.setEta_rate(19.24);
            month_8_gridOut.setEta_percent("19.24%");
            gridOut_list.add(month_8_gridOut);

            HalmEtaTrendReportGridOut month_9_gridOut = new HalmEtaTrendReportGridOut();
            month_9_gridOut.setMonth(getMonthCode("09"));
            month_9_gridOut.setEta_rate(19.26);
            month_9_gridOut.setEta_percent("19.26%");
            gridOut_list.add(month_9_gridOut);

            HalmEtaTrendReportGridOut month_10_gridOut = new HalmEtaTrendReportGridOut();
            month_10_gridOut.setMonth(getMonthCode("10"));
            month_10_gridOut.setEta_rate(19.27);
            month_10_gridOut.setEta_percent("19.27%");
            gridOut_list.add(month_10_gridOut);

            HalmEtaTrendReportGridOut month_11_gridOut = new HalmEtaTrendReportGridOut();
            month_11_gridOut.setMonth(getMonthCode("11"));
            month_11_gridOut.setEta_rate(19.26);
            month_11_gridOut.setEta_percent("19.26%");
            gridOut_list.add(month_11_gridOut);
        }
    }


    /*** 获取月份的英文缩写 **/
    private String getMonthCode(String month) {
        switch (month==null?"":month) {
            case "01" : return "Jan";
            case "02" : return "Feb";
            case "03" : return "Mar";
            case "04" : return "Apr";
            case "05" : return "May";
            case "06" : return "June";
            case "07" : return "July";
            case "08" : return "August";
            case "09" : return "Sept";
            case "10" : return "Oct";
            case "11" : return "Nov";
            case "12" : return "Dec";
            default   : return month;
        }
    }
}
